﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;

namespace BoAddonBiz_FreightForwarder.Air.AirPortWork.Warehouse
{
    class B1Form_StorageImport : SAPBoAddon.B1Form
    {
        #region 继承至基类
        protected override bool BeforeSaveCheckFormData()
        {
            return true;
        }

        protected override void FormEditModeChange(SAPBoAddon.frmItemEditMode frmEditMode)
        {
        }

        protected override void FormInfoSetting(SAPBoAddon.B1FormSetting FormSetting)
        {
            FormSetting.FormType = B1FormTypes.ado_AVA_FF_StorageImport;
            FormSetting.FormFileName = FormSetting.FormType + "Form.srf";
        }

        protected override void InitializeForm(SAPBoAddon.B1FormInitializePar InitPar)
        {
        }
        #endregion
        public B1Form_StorageImport()
        {
            this.frm_ItemEvent += new SAPBoAddon.IB1FormEvents.B1ItemEventEventHandler(B1Form_StorageImport_frm_ItemEvent);
        }

        void B1Form_StorageImport_frm_ItemEvent(SAPBoAddon.B1AddonBase.B1ItemEvent pVal, ref bool BubbleEvent)
        {
            if (pVal.BeforeAction)
            {

            }
            else
            {
                if (pVal.ItemUID == "7" && pVal.EventType == SAPbouiCOM.BoEventTypes.et_ITEM_PRESSED)
                {
                    string FilePath = string.Empty;
                    FilePath = SAPBoAddon.B1Assistant.WinForm.WinForm_GetOpenFileName();
                    SAPBoAddon.B1Assistant.Item.SetValue(this.CurrentForm, "4", FilePath);
                }
                if (pVal.ItemUID == "8" && pVal.EventType == SAPbouiCOM.BoEventTypes.et_ITEM_PRESSED)
                {
                    string FilePath = string.Empty;
                    FilePath = SAPBoAddon.B1Assistant.WinForm.WinForm_GetOpenFileName();
                    SAPBoAddon.B1Assistant.Item.SetValue(this.CurrentForm, "6", FilePath);
                }
                if (pVal.ItemUID == "btn_import" && pVal.EventType == SAPbouiCOM.BoEventTypes.et_ITEM_PRESSED)
                {
                    SAPBoAddon.B1Assistant.Item.SetEnabled(this.CurrentForm, pVal.ItemUID, false);
                    this.ImportWarehouseInOutTime();
                    this.ImportSEDLTime();
                    SAPBoAddon.B1Assistant.Item.SetEnabled(this.CurrentForm, pVal.ItemUID, true);
                }
            }
        }

        private void ImportWarehouseInOutTime()
        {
            SAPbobsCOM.Recordset oRs = SBOCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
            string fileName = SAPBoAddon.B1Assistant.Item.GetValue(this.CurrentForm, "4").ToString().Trim();
            if (string.IsNullOrWhiteSpace(fileName))
            {
                return;
            }
            SBOApp.StatusBar.SetText("正在更新 出入库时间...", SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Warning);
            string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=True;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;' ", fileName);

            System.Data.OleDb.OleDbConnection conn;
            string template_UpdOBNO1 = @"UPDATE T2 SET T2.U_InDate=N'{0}',T2.U_InTime={1}  FROM [@AVA_FF_OSTG] T1 INNER JOIN [@AVA_FF_STG1] T2 ON T1.DocEntry=T2.DocEntry  WHERE T1.U_BaseSerial=N'{2}';   " + System.Environment.NewLine;
            string template_UpdOBNO2 = @"UPDATE T2 SET T2.U_DeliveryDate=N'{0}',T2.U_DeliveryTime={1}  FROM [@AVA_FF_OSTG] T1 INNER JOIN [@AVA_FF_STG1] T2 ON T1.DocEntry=T2.DocEntry  WHERE T1.U_BaseSerial=N'{2}';    " + System.Environment.NewLine;

            //            string template_UpdOBNO1 =
            //            @"UPDATE T2 SET T2.U_InDate=N'{0}',T2.U_InTime={1} 
            //            FROM [@AVA_FF_OSTG] T1 INNER JOIN [@AVA_FF_STG1] T2 ON T1.DocEntry=T2.DocEntry
            //            WHERE T1.U_BaseSerial=N'{2}';
            //            ";
            //            string template_UpdOBNO2 =
            //            @"UPDATE T2 SET T2.U_DeliveryDate=N'{0}',T2.U_DeliveryTime={1} 
            //            FROM [@AVA_FF_OSTG] T1 INNER JOIN [@AVA_FF_STG1] T2 ON T1.DocEntry=T2.DocEntry
            //            WHERE T1.U_BaseSerial=N'{2}';
            //            ";

            string SBLNum = string.Empty, SQL_UpdOBNO = string.Empty, outOrIn = string.Empty, outOrInDateTime = string.Empty, col1Text = string.Empty, strTime = string.Empty;
            int spaceIndex = 0, count = 1;
            using (conn = new System.Data.OleDb.OleDbConnection(strConn))
            {
                conn.Open();
                System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string TableName = dt.Rows[0]["TABLE_NAME"].ToString();
                OleDbCommand command = new OleDbCommand(string.Format("SELECT * FROM [{0}]", TableName), conn);

                OleDbDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    col1Text = Convert.ToString(reader.GetValue(0));
                    if (string.IsNullOrWhiteSpace(col1Text))
                    {
                        break;
                    }
                    SBLNum = col1Text.Substring(col1Text.IndexOf("_") + 1).Trim();
                    outOrIn = reader.GetString(1);
                    outOrInDateTime = Convert.ToString(reader.GetValue(2));
                    spaceIndex = outOrInDateTime.IndexOf(" ");
                    strTime = outOrInDateTime.Substring(spaceIndex + 1, outOrInDateTime.LastIndexOf(":") - spaceIndex).Replace(":", string.Empty).Replace("：", string.Empty);

                    if (outOrIn == "入库")
                    {
                        SQL_UpdOBNO += string.Format(template_UpdOBNO1, outOrInDateTime.Substring(0, spaceIndex), strTime, SBLNum);
                    }
                    if (outOrIn == "出库")
                    {
                        SQL_UpdOBNO += string.Format(template_UpdOBNO2, outOrInDateTime.Substring(0, spaceIndex), strTime, SBLNum);
                    }
                    count++;
                }
                reader.Close();
                oRs.DoQuery(SQL_UpdOBNO);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oRs);
                SBOApp.StatusBar.SetText("出入库时间  更新完成.", SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Success);
            }
        }

        private void ImportSEDLTime()
        {
            SAPbobsCOM.Recordset oRs = SBOCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
            string fileName = SAPBoAddon.B1Assistant.Item.GetValue(this.CurrentForm, "6").ToString().Trim();
            if (string.IsNullOrWhiteSpace(fileName))
            {
                return;
            }
            SBOApp.StatusBar.SetText("正在更新 通知时间...", SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Warning);
            string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=True;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;' ", fileName);

            System.Data.OleDb.OleDbConnection conn;

            string template_UpdSTG1 = @"UPDATE T2 SET T2.U_RCTime={0}   FROM [@AVA_FF_OSTG] T1 INNER JOIN [@AVA_FF_STG1] T2 ON T1.DocEntry=T2.DocEntry   WHERE T1.U_BaseSerial=N'{1}'; " + System.Environment.NewLine;

            //            string template_UpdSTG1 =
            //            @"UPDATE T2 SET T2.U_RCTime={0} 
            //            FROM [@AVA_FF_OSTG] T1 INNER JOIN [@AVA_FF_STG1] T2 ON T1.DocEntry=T2.DocEntry
            //            WHERE T1.U_BaseSerial=N'{1}';
            //            ";

            string SBLNum = string.Empty, SQL_UpdSTG1 = string.Empty, noticeTime = string.Empty;
            using (conn = new System.Data.OleDb.OleDbConnection(strConn))
            {
                OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
                conn.Open();
                OleDbDataReader reader = command.ExecuteReader();
                reader.Read();
                while (reader.Read())
                {
                    if (reader.IsDBNull(2))
                    {
                        break;
                    }
                    noticeTime = string.Format("{0}{1}", reader.GetDateTime(2).Hour, reader.GetDateTime(2).Minute);
                    SBLNum = reader.GetString(3).Trim();

                    SQL_UpdSTG1 += string.Format(template_UpdSTG1, noticeTime.Replace(":", string.Empty).Replace("：", string.Empty), SBLNum);
                }
                reader.Close();
                oRs.DoQuery(SQL_UpdSTG1);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oRs);
                SBOApp.StatusBar.SetText("通知时间  更新完成.", SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Success);
            }
        }
    }
}
